package ch.ethz.inf.shop.model.simpleDatabase.operators.tests;

import java.text.ParseException;

import junit.framework.Assert;

import org.junit.Test;

import ch.ethz.inf.shop.model.simpleDatabase.Tuple;
import ch.ethz.inf.shop.model.simpleDatabase.operators.Join;
import ch.ethz.inf.shop.model.simpleDatabase.operators.Operator;
import ch.ethz.inf.shop.model.simpleDatabase.operators.Scan;

public class TestJoin {

	@Test
	public void JoinThreeWayJoin() {
		Operator leftOp = new Scan(AllTests.ROOT_DIRECTORY + "/recipe.txt", "recipe");
		Operator rightOp = new Scan(AllTests.ROOT_DIRECTORY + "/recipe_item.txt", "recipe_item");

		leftOp = new Join(leftOp, "recipe.recipe_id", rightOp, "recipe_item.recipe");

		rightOp = new Scan(AllTests.ROOT_DIRECTORY + "/product.txt", "product");

		leftOp = new Join(leftOp, "recipe_item.product", rightOp, "product.product_id");
		int cnt = 0;
		while (leftOp.moveNext()) {
			// System.out.println(leftOp.current());
			cnt++;
		}
		Assert.assertEquals(18, cnt);
	}

	@Test
	public void JoinBrandProductCategoryTest() {
		Operator leftOp = new Scan(AllTests.ROOT_DIRECTORY + "/product.txt", "product");
		Operator rightOp = new Scan(AllTests.ROOT_DIRECTORY + "/brand.txt", "brand");

		leftOp = new Join(leftOp, "product.brand", rightOp, "brand.brand_id");
		rightOp = new Scan(AllTests.ROOT_DIRECTORY + "/category.txt", "category");

		Operator j = new Join(leftOp, "product.category", rightOp, "category.category_id");
		int cnt = 1;
		Assert.assertTrue(j.moveNext());
		Assert.assertEquals(11, j.current().getSchema().getColumnNames().size());
		Assert.assertEquals(11, j.current().getValues().size());
		while (j.moveNext()) {
			cnt++;
		}
		Assert.assertEquals(12, cnt);

		// test order of operators does not matter
		leftOp = new Scan(AllTests.ROOT_DIRECTORY + "/product.txt", "product");
		rightOp = new Scan(AllTests.ROOT_DIRECTORY + "/category.txt", "category");

		leftOp = new Join(leftOp, "product.category", rightOp, "category.category_id");
		rightOp = new Scan(AllTests.ROOT_DIRECTORY + "/brand.txt", "brand");

		j = new Join(leftOp, "product.brand", rightOp, "brand.brand_id");
		cnt = 1;
		Assert.assertTrue(j.moveNext());
		Assert.assertEquals(11, j.current().getSchema().getColumnNames().size());
		Assert.assertEquals(11, j.current().getValues().size());
		while (j.moveNext()) {
			cnt++;
		}
		Assert.assertEquals(12, cnt);

		// test not matter whether left or right
		leftOp = new Scan(AllTests.ROOT_DIRECTORY + "/product.txt", "product");
		rightOp = new Scan(AllTests.ROOT_DIRECTORY + "/category.txt", "category");

		rightOp = new Join(leftOp, "product.category", rightOp, "category.category_id");
		leftOp = new Scan(AllTests.ROOT_DIRECTORY + "/brand.txt", "brand");

		j = new Join(leftOp, "brand.brand_id", rightOp, "product.brand");
		cnt = 1;
		Assert.assertTrue(j.moveNext());
		Assert.assertEquals(11, j.current().getSchema().getColumnNames().size());
		Assert.assertEquals(11, j.current().getValues().size());
		while (j.moveNext()) {
			cnt++;
		}
		Assert.assertEquals(12, cnt);
	}

	@Test
	public void JoinProductBrandTest() {

		// test if leftOp and rightOp exchange makes no difference
		Scan leftOp = new Scan(AllTests.ROOT_DIRECTORY + "/product.txt", "product");
		Scan rightOp = new Scan(AllTests.ROOT_DIRECTORY + "/brand.txt", "brand");

		Join j = new Join(leftOp, "product.brand", rightOp, "brand.brand_id");
		int cnt = 0;
		while (j.moveNext()) {
			cnt++;
		}
		Assert.assertEquals(12, cnt);

		leftOp = new Scan(AllTests.ROOT_DIRECTORY + "/brand.txt", "brand");
		rightOp = new Scan(AllTests.ROOT_DIRECTORY + "/product.txt", "product");

		j = new Join(leftOp, "brand.brand_id", rightOp, "product.brand");
		cnt = 0;
		while (j.moveNext()) {
			cnt++;
		}
		Assert.assertEquals(12, cnt);

	}

	@Test
	public void JoinCommentsAndUsersTest() throws ParseException {
		Scan leftOp = new Scan(AllTests.ROOT_DIRECTORY + "/registered_user.txt", "registered_user");
		Scan rightOp = new Scan(AllTests.ROOT_DIRECTORY + "/comment.txt", "comment");

		Join j = new Join(leftOp, "registered_user.user_id", rightOp, "comment.user");

		Assert.assertTrue(j.moveNext());
		Assert.assertEquals(1, j.current().getInt(0));
		Assert.assertEquals("rop", j.current().getString(1));
		Assert.assertEquals("rop", j.current().getString(2));
		Assert.assertEquals("philipp.rohr@gmail.com", j.current().getString(3));
		Assert.assertEquals(1, j.current().getInt(4));
		Assert.assertEquals(1, j.current().getInt(5));
		Assert.assertEquals(1, j.current().getInt(6));
		Assert.assertEquals("Wie sehr ich diesen Thun doch mag! Vor allem den leckeren Delfin darin!", j.current().getString(7));
		Assert.assertEquals(Tuple.DATE_FORMAT.parse("2012-03-25 10:30:34"), j.current().getDate(8));

		Assert.assertTrue(j.moveNext());
		Assert.assertEquals(2, j.current().getInt(0));
		Assert.assertEquals("patric", j.current().getString(1));
		Assert.assertEquals("patric", j.current().getString(2));
		Assert.assertEquals("patric.rousselot@gmail.com", j.current().getString(3));
		Assert.assertEquals(2, j.current().getInt(4));
		Assert.assertEquals(4, j.current().getInt(5));
		Assert.assertEquals(2, j.current().getInt(6));
		Assert.assertEquals("Diese Pizza schmeckt scheusslich. Ich will einen dislike button!", j.current().getString(7));
		Assert.assertEquals(Tuple.DATE_FORMAT.parse("2012-03-25 10:30:34"), j.current().getDate(8));

		Assert.assertTrue(j.moveNext());
		Assert.assertEquals(2, j.current().getInt(0));
		Assert.assertEquals("patric", j.current().getString(1));
		Assert.assertEquals("patric", j.current().getString(2));
		Assert.assertEquals("patric.rousselot@gmail.com", j.current().getString(3));
		Assert.assertEquals(3, j.current().getInt(4));
		Assert.assertEquals(12, j.current().getInt(5));
		Assert.assertEquals(2, j.current().getInt(6));
		Assert.assertEquals("papapapapaprika!", j.current().getString(7));
		Assert.assertEquals(Tuple.DATE_FORMAT.parse("2012-03-25 10:34:50"), j.current().getDate(8));

		Assert.assertTrue(j.moveNext());
		Assert.assertEquals(2, j.current().getInt(0));
		Assert.assertEquals("patric", j.current().getString(1));
		Assert.assertEquals("patric", j.current().getString(2));
		Assert.assertEquals("patric.rousselot@gmail.com", j.current().getString(3));
		Assert.assertEquals(4, j.current().getInt(4));
		Assert.assertEquals(12, j.current().getInt(5));
		Assert.assertEquals(2, j.current().getInt(6));
		Assert.assertEquals("double like!", j.current().getString(7));
		Assert.assertEquals(Tuple.DATE_FORMAT.parse("2012-03-25 10:35:13"), j.current().getDate(8));

		Assert.assertTrue(j.moveNext());
		Assert.assertEquals(2, j.current().getInt(0));
		Assert.assertEquals("patric", j.current().getString(1));
		Assert.assertEquals("patric", j.current().getString(2));
		Assert.assertEquals("patric.rousselot@gmail.com", j.current().getString(3));
		Assert.assertEquals(5, j.current().getInt(4));
		Assert.assertEquals(3, j.current().getInt(5));
		Assert.assertEquals(2, j.current().getInt(6));
		Assert.assertEquals("is there also a vegetarian pig?", j.current().getString(7));
		Assert.assertEquals(Tuple.DATE_FORMAT.parse("2012-03-27 23:05:35"), j.current().getDate(8));
		Assert.assertFalse(j.moveNext());
	}
}
